package com.nf.dao.impl;

import com.nf.dao.OrderInfoDao;
import com.nf.entity.OrderInfo;
import com.nf.util.DBUtil;
import com.nf.util.PagerUtil;
import com.nf.vo.OrdersVo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrderInfoDaoImpl implements OrderInfoDao {
    @Override
    public int add(OrderInfo order) {
        int id=0;

        Connection conn= DBUtil.getConnection();
        String sql="insert into orderInfo values(default,?,?,?,?,?,?,default,?,?,?)";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, order.getFilmName());
            pstmt.setString(2,order.getHallName());
            pstmt.setString(3,order.getShowDate());
            pstmt.setString(4,order.getStartTime());
            pstmt.setFloat(5,order.getPrice());
            pstmt.setString(6,order.getSeats());
            pstmt.setInt(7,order.getUserId());
            pstmt.setInt(8,order.getShowId());
            pstmt.setInt(9,order.getFilmId());

            pstmt.executeUpdate();
            rst= pstmt.getGeneratedKeys();
            if(rst.next()){
                id=rst.getInt(1);

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,pstmt,conn);
        }
        return id;
    }

    @Override
    public OrderInfo selectById(int orderId) {
        OrderInfo order=null;

        Connection conn=DBUtil.getConnection();
        String sql="select * from orderInfo where order_id=?";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,orderId);
            rst= pstmt.executeQuery();
            if(rst.next()){
                String orderTime=rst.getString(2);
                String filmName=rst.getString(3);
                String hallName=rst.getString(4);
                String showDate=rst.getString(5);
                String startTime=rst.getString(6);
                Float price=rst.getFloat(7);
                String seats=rst.getString(8);
                int status=rst.getInt(9);
                int userId=rst.getInt(10);
                int showId=rst.getInt(11);
                int filmId=rst.getInt(12);

                order=new OrderInfo(orderId,orderTime,filmName,hallName,showDate,startTime,price,seats,status,userId,showId,filmId);

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }

        return order;
    }

    @Override
    public int del(int orderId) {
        int r=0;

        Connection conn=DBUtil.getConnection();
        String sql="delete from orderInfo where order_id=?";
        PreparedStatement pstmt=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,orderId);
            r= pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,pstmt,conn);
        }

        return r;
    }

    @Override
    public int updateStatus(int orderId) {
        int r=0;

        Connection conn=DBUtil.getConnection();
        String sql="update orderInfo set status=1 where order_id=?";
        PreparedStatement pstmt=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,orderId);
            r=pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(null,pstmt,conn);
        }

        return r;
    }

    @Override
    public List<OrdersVo> selectByUserId(int userId) {
        List<OrdersVo> list=new ArrayList<>();

        Connection conn=DBUtil.getConnection();
        String sql="select o.order_id,o.order_time,o.film_name,f.film_picture,o.hall_name,o.show_date,o.start_time,o.price,o.seats,o.status,o.user_id,o.show_id,o.film_id from orderInfo o,filmInfo f where o.film_id=f.film_id and o.user_id=? order by o.order_id desc";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,userId);
            rst= pstmt.executeQuery();
            while (rst.next()){
                int orderId=rst.getInt(1);
                String orderTime=rst.getString(2);
                String filmName=rst.getString(3);
                String filmPicture=rst.getString(4);
                String hallName=rst.getString(5);
                String showDate=rst.getString(6);
                String startTime=rst.getString(7);
                Float price=rst.getFloat(8);
                String seats=rst.getString(9);
                int status=rst.getInt(10);
                int userid=rst.getInt(11);
                int showId=rst.getInt(12);
                int filmId=rst.getInt(13);

                OrdersVo order=new OrdersVo(orderId,orderTime,filmName,filmPicture,hallName,showDate,startTime,price,seats,status,userid,showId,filmId);
                list.add(order);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }
        return list;
    }

    @Override
    public OrderInfo selectBystatus() {
        OrderInfo order=null;

        Connection conn=DBUtil.getConnection();
        String sql="select * from orderInfo where status=0";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt=conn.prepareStatement(sql);

            rst= pstmt.executeQuery();
            if (rst.next()){
                int orderId=rst.getInt(1);
                String orderTime=rst.getString(2);
                String filmName=rst.getString(3);
                String hallName=rst.getString(4);
                String showDate=rst.getString(5);
                String startTime=rst.getString(6);
                Float price=rst.getFloat(7);
                String seats=rst.getString(8);
                int status=rst.getInt(9);
                int userId=rst.getInt(10);
                int showId=rst.getInt(11);
                int filmId=rst.getInt(12);

                order=new OrderInfo(orderId,orderTime,filmName,hallName,showDate,startTime,price,seats,status,userId,showId,filmId);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }

        return order;
    }

    @Override
    public List<OrderInfo> selectAll() {
        List<OrderInfo> list=new ArrayList<>();

        Connection conn=DBUtil.getConnection();
        String sql="select * from orderInfo order by order_id desc";
        PreparedStatement pstmt=null;
        ResultSet rst=null;
        try {
            pstmt= conn.prepareStatement(sql);
            rst= pstmt.executeQuery();
            while (rst.next()){
                int orderId=rst.getInt(1);
                String orderTime=rst.getString(2);
                String filmName=rst.getString(3);
                String hallName=rst.getString(4);
                String showDate=rst.getString(5);
                String startTime=rst.getString(6);
                Float price= rst.getFloat(7);
                String seats=rst.getString(8);
                int status=rst.getInt(9);
                int userId=rst.getInt(10);
                int showId=rst.getInt(11);
                int filmId=rst.getInt(12);

                OrderInfo order=new OrderInfo(orderId,orderTime,filmName,hallName,showDate,startTime,price,seats,status,userId,showId,filmId);
                list.add(order);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }

        return list;
    }

    @Override
    public PagerUtil<OrderInfo> selectByPage(int currentPage, int pageCount) {
        // 创建分页对象，并初始化分页的信息
        PagerUtil<OrderInfo> pager = new PagerUtil<OrderInfo>();

        // 封装总记录数
        pager.setTotalRecords(getCount());
        // 封装每页显示的记录数
        pager.setPageCount(pageCount);
        // 封装当前页
        pager.setCurrentPage(currentPage);

        List<OrderInfo> list =new  ArrayList<OrderInfo>();

        String sql = "select * from ( select *, row_number() over(order by a.order_id desc) as rownumber from orderInfo as a) as b where rownumber between  ? and ?" ;
        Connection conn = DBUtil.getConnection();

        PreparedStatement pstmt = null ;
        ResultSet rst = null ;

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, (currentPage - 1) * pageCount + 1);
            pstmt.setInt(2, currentPage * pageCount);
            rst = pstmt.executeQuery();

            while (rst.next()) {

                int orderId=rst.getInt(1);
                String orderTime=rst.getString(2);
                String filmName=rst.getString(3);
                String hallName=rst.getString(4);
                String showDate=rst.getString(5);
                String startTime=rst.getString(6);
                Float price= rst.getFloat(7);
                String seats=rst.getString(8);
                int status=rst.getInt(9);
                int userId=rst.getInt(10);
                int showId=rst.getInt(11);
                int filmId=rst.getInt(12);

                OrderInfo order=new OrderInfo(orderId,orderTime,filmName,hallName,showDate,startTime,price,seats,status,userId,showId,filmId);
                list.add(order);
            }

            // 把当前页中的数据，封装起来
            pager.setData(list);

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            DBUtil.close(rst, pstmt, conn);
        }

        return pager;
    }

    @Override
    public int getCount() {
        int r = 0 ;

        String sql ="select COUNT(order_id) from orderInfo";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rst =null;
        try {
            pstmt = conn.prepareStatement(sql);
            rst = pstmt.executeQuery();
            if(rst.next()) {

                r=rst.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rst, pstmt, conn);
        }
        return r;
    }

    @Override
    public List<OrderInfo> selectByLike(String cond) {
        List<OrderInfo> list=new ArrayList<>();

        Connection conn=DBUtil.getConnection();
        String sql="select * from orderInfo where film_name like ? or hall_name like ? or order_time like ? or show_date like ? order by order_id desc";
        PreparedStatement pstmt=null;
        ResultSet rst=null;

        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,"%"+cond+"%");
            pstmt.setString(2,"%"+cond+"%");
            pstmt.setString(3,"%"+cond+"%");
            pstmt.setString(4,"%"+cond+"%");
            rst= pstmt.executeQuery();
            while (rst.next()){
                int orderId=rst.getInt(1);
                String orderTime=rst.getString(2);
                String filmName=rst.getString(3);
                String hallName=rst.getString(4);
                String showDate=rst.getString(5);
                String startTime=rst.getString(6);
                Float price= rst.getFloat(7);
                String seats=rst.getString(8);
                int status=rst.getInt(9);
                int userId=rst.getInt(10);
                int showId=rst.getInt(11);
                int filmId=rst.getInt(12);

                OrderInfo order=new OrderInfo(orderId,orderTime,filmName,hallName,showDate,startTime,price,seats,status,userId,showId,filmId);
                list.add(order);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil.close(rst,pstmt,conn);
        }
        return list;
    }

}
